# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: http://doc.scrapy.org/en/latest/topics/item-pipeline.html
import json
import codecs
import logging

from twisted.enterprise.adbapi import ConnectionPool

from pangolin.common.db_operateCommon import *

# from pangolin.settings import db_conn
from pangolin.settings import IS_SAVE_DATABASE
from pangolin.settings import IS_SAVE_SERVER
from pangolin.settings import DB_CONF
from pangolin.common.http_common import *


class PangolinJosnPipeline(object):
    def __init__(self):
        self.file = codecs.open('data.json', mode='wb',encoding='utf-8')

    def process_item(self, item, spider):
        line = json.dumps(dict(item), ensure_ascii=False)+'\n'# ensure_ascii=False很重要
        self.file.write(line)
        return item

    def close_spider(self,spider):
        self.file.close()


class PangolinPipeline(object):

    def __init__(self):
        self.dbpool = ReconnectingConnectionPool('MySQLdb',
                                                 host=DB_CONF['host'],
                                                 db=DB_CONF['db'],
                                                 user=DB_CONF['user'],
                                                 passwd=DB_CONF['passwd'],
                                                 port=DB_CONF['port'],
                                                 cursorclass=DB_CONF['cursorclass'],
                                                 charset=DB_CONF['charset'],
                                                 use_unicode=DB_CONF['use_unicode'],
                                                 cp_reconnect=True)
        self.save_database = IS_SAVE_DATABASE
        self.save_server = IS_SAVE_SERVER

    def process_item(self, item, spider):
        query = self.dbpool.runInteraction(self._conditional_insert, item)
        query.addErrback(self.handle_error)
        return item

    def _conditional_insert(self, tx, item):

        if self.save_database == 'TRUE':
            if item['data_type'] == "TicketList":
                tx.execute( \
                        "insert into "+item['product_list_table']+" (id,company_code,platform_code,city_code,scenery_name,product_id,detail_url,update_time,task_id,task_date) values (%s, %s ,%s, %s, %s, %s, %s ,%s, %s, %s)",\
                        (item['ids'], item['company_code'], item['platform_code'],item['city_code'],item['scenery_name'],item['product_id'],item['detail_url'],item['update_time'],item['task_id'],item['task_date']))
                # logging.info("Item stored in db: %s" % item)
            elif item['data_type'] == "TicketDetail":
                logging.info("detail_url:%s" % item['detail_url'])
                tx.execute( \
                    "insert into " + item[
                        'product_table'] + " (detail_id,company_code,platform_code,city_code,scenery_name, scenery_addr, product_id, detail_url,lowest_price,score, image_num, introduction,score_tag,star_level ,update_time ,task_id ,task_date, snapshot_addr, lv_product_id) values (%s, %s, %s ,%s, %s, %s, %s, %s ,%s,  %s, %s, %s ,%s, %s, %s, %s, %s, %s, %s)", \
                    (item['detail_id'], item['company_code'], item['platform_code'], item['city_code'],
                     item['scenery_name'], item['scenery_addr'], item['product_id'], item['detail_url'],
                     item['lowest_price'], item['score'], item['image_num'], item['introduction'], item['score_tag'],
                     item['star_level'], item['update_time'], item['task_id'], item['task_date'],
                     item['snapshot_addr'], item['lv_product_id']))
                if len(item['commodity_type']) > 0:
                    for i in range(0, len(item['commodity_type'])):
                        if i == 0 and 'booking_effective_time' not in item.keys():
                            item['booking_effective_time'] = ["" for i in range(len(item['commodity_type']))]
                        tx.execute( \
                            "insert into " + item[
                                'commodity_table'] + " (detail_id, commodity_type, commodity_id, commodity_name, commodity_url, product_id, sale_cond, refund_cond, rack_rate,sale_price,preferential,update_time,task_id,task_date,city_code,booking_effective_time) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)", \
                            (item['detail_id'], item['commodity_type'][i], item['commodity_id'][i],
                             item['commodity_name'][i], item['commodity_url'][i], item['product_id'],
                             item['sale_cond'][i], item['refund_cond'][i], item['rack_rate'][i], item['sale_price'][i],
                             item['preferential'][i], item['update_time'], item['task_id'], item['task_date'],
                             item['city_code'], item['booking_effective_time'][i]))
            elif item['data_type'] == "HotelDetail":
                logging.info("detail_url:%s" % item['detail_url'])
                tx.execute("insert into " + item['product_table'] +
                           " (detail_id,company_code,platform_code,city_code,hotel_name,come_date,leave_date,"
                           "hotel_addr,product_id,detail_url,lowest_price,score,image_num,introduction,score_tag,"
                           "star_level ,update_time ,task_id ,task_date, snapshot_addr, lv_product_id) values "
                           "(%s, %s, %s ,%s, %s, %s, %s, %s ,%s,%s, %s, %s ,%s, %s, %s, %s, %s, %s, %s, %s, %s)",
                           (item['detail_id'], item['company_code'], item['platform_code'], item['city_code'],
                            item['hotel_name'], item['come_date'], item['leave_date'], item['hotel_addr'],
                            item['product_id'], item['detail_url'], item['lowest_price'], item['score'],
                            item['image_num'], item['introduction'], item['score_tag'], item['star_level'],
                            item['update_time'], item['task_id'], item['task_date'], item['snapshot_addr'],
                            item['lv_product_id']))
                if len(item['room_type']) > 0:
                    for i in range(0, len(item['room_type'])):
                        tx.execute("insert into " + item['commodity_table'] +
                                   " (detail_id,room_type,commodity_id,area,floor,commodity_name,bed_type,broadband,"
                                   "breakfast,window,increase_bed,people_num,commodity_url,product_id,cancel_rule,"
                                   "preferential,sale_price,update_time,task_id,task_date,city_code,company_code) values "
                                   "(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",
                                   (item['detail_id'], item['room_type'][i], item['commodity_id'][i], item['area'][i],
                                    item['floor'][i], item['commodity_name'][i], item['bed_type'][i],
                                    item['broadband'][i], item['breakfast'][i], item['window'][i],
                                    item['increase_bed'][i], item['people_num'][i], item['commodity_url'][i],
                                    item['product_id'], item['cancel_rule'][i], item['preferential'][i],
                                    item['sale_price'][i], item['update_time'], item['task_id'], item['task_date'],
                                    item['city_code'], item['company_code']))
        if self.save_server == 'TRUE':
            http_common = HttpCommon()
            if item['data_type'] == "TicketDetail" or item['data_type'] == "HotelDetail":
                # if item['product_table'] == "ticket_pdetail_tc" and item['platform_code'] == "WAP":
                #     pass # 临时控制TC WAP数据不回传代码
                # else:
                #     http_common.get_method(item)
                http_common.get_method(item)

    def handle_error(self, e):
        logging.error("SQL错误:%s" % e)


class ReconnectingConnectionPool(ConnectionPool):
    # Reconnecting adbapi connection pool for MySQL.

    def _runInteraction(self, interaction, *args, **kw):
        try:
            return ConnectionPool._runInteraction(self, interaction, *args, **kw)
        except MySQLdb.OperationalError as e:

            # 2006 MySQL server has gone away
            # 2013 Lost connection to MySQL server
            # 1213 Deadlock found when trying to get lock; try restarting transaction

            if e[0] not in (2006, 2013, 1213):
                raise

            logging.error("%s got error %s, retrying operation.  If no errors follow, retry was successful." % (
            self.__class__.__name__, e))
            conn = self.connections.get(self.threadID())
            self.disconnect(conn)
            # try the interaction again
            return ConnectionPool._runInteraction(self, interaction, *args, **kw)
        except MySQLdb.InterfaceError as e:

            # 0 Interface error (conn gone away or closed)
            if e[0] not in (0,):
                raise

            logging.error("%s got error %s, retrying operation.  If no errors follow, retry was successful." % (
            self.__class__.__name__, e))
            conn = self.connections.get(self.threadID())
            self.disconnect(conn)
            # try the interaction again
            return ConnectionPool._runInteraction(self, interaction, *args, **kw)
